<!DOCTYPE html>
<html>
  <head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0" name="viewport">
  <meta name="description" content="刘清政">
  <meta name="keyword" content="hexo-theme">
  
    <link rel="shortcut icon" href="/css/images/logo.png">
  
  <title>
    
      db/MySQL系列/06-MySQL系列之-日志管理 | Justin-刘清政的博客
    
  </title>
  <link href="//cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.css" rel="stylesheet">
  <link href="//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/styles/tomorrow.min.css" rel="stylesheet">
  
<link rel="stylesheet" href="/css/style.css">

  
    
<link rel="stylesheet" href="/css/plugins/gitment.css">

  
  <script src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/geopattern/1.2.3/js/geopattern.min.js"></script>
  <script src="//cdnjs.cloudflare.com/ajax/libs/nprogress/0.2.0/nprogress.min.js"></script>
  
    
<script src="/js/qrious.js"></script>

  
  
    
<script src="/js/gitment.js"></script>

  
  

  
<meta name="generator" content="Hexo 4.2.0"></head>
<div class="wechat-share">
  <img src="/css/images/logo.png" />
</div>

  <body>
    <header class="header fixed-header">
  <div class="header-container">
    <a class="home-link" href="/">
      <div class="logo"></div>
      <span>Justin-刘清政的博客</span>
    </a>
    <ul class="right-list">
      
        <li class="list-item">
          
            <a href="/" class="item-link">主页</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/tags/" class="item-link">标签</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/archives/" class="item-link">归档</a>
          
        </li>
      
        <li class="list-item">
          
            <a href="/about/" class="item-link">关于我</a>
          
        </li>
      
    </ul>
    <div class="menu">
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
      <span class="icon-bar"></span>
    </div>
    <div class="menu-mask">
      <ul class="menu-list">
        
          <li class="menu-item">
            
              <a href="/" class="menu-link">主页</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/tags/" class="menu-link">标签</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/archives/" class="menu-link">归档</a>
            
          </li>
        
          <li class="menu-item">
            
              <a href="/about/" class="menu-link">关于我</a>
            
          </li>
        
      </ul>
    </div>
  </div>
</header>

    <div id="article-banner">
  <h2>db/MySQL系列/06-MySQL系列之-日志管理</h2>



  <p class="post-date">2019-12-24</p>
    <!-- 不蒜子统计 -->
    <span id="busuanzi_container_page_pv" style='display:none' class="">
        <i class="icon-smile icon"></i> 阅读数：<span id="busuanzi_value_page_pv"></span>次
    </span>
  <div class="arrow-down">
    <a href="javascript:;"></a>
  </div>
</div>
<main class="app-body flex-box">
  <!-- Article START -->
  <article class="post-article">
    <section class="markdown-content"><p><img src="https:////upload-images.jianshu.io/upload_images/16956686-062f0a6b2bc97bb2.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/605/format/webp" alt="img"></p>
<p>image.png</p>
<h1 id="1-错误日志-log-error"><a href="#1-错误日志-log-error" class="headerlink" title="1.错误日志(log_error)"></a>1.错误日志(log_error)</h1><h2 id="1-1-作用"><a href="#1-1-作用" class="headerlink" title="1.1 作用"></a>1.1 作用</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">记录启动\关闭\日常运行过程中,状态信息,警告,错误</span><br></pre></td></tr></table></figure>

<h2 id="1-2-错误日志配置"><a href="#1-2-错误日志配置" class="headerlink" title="1.2 错误日志配置"></a>1.2 错误日志配置</h2><figure class="highlight dart"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">默认就是开启的:  /数据路径下/hostname.err</span><br><span class="line">手工设定:</span><br><span class="line">Master [(none)]&gt;select @<span class="meta">@log</span>_error;</span><br><span class="line">vim /etc/my.cnf</span><br><span class="line">log_error=/<span class="keyword">var</span>/log/mysql.log</span><br><span class="line">log_timestamps=system</span><br><span class="line">重启生效</span><br><span class="line"><span class="keyword">show</span> variables like <span class="string">'log_error'</span>;</span><br></pre></td></tr></table></figure>

<h2 id="1-3-日志内容查看"><a href="#1-3-日志内容查看" class="headerlink" title="1.3 日志内容查看"></a>1.3 日志内容查看</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">主要关注<span class="selector-attr">[ERROR]</span>,看上下文</span><br></pre></td></tr></table></figure>

<h1 id="2-binlog-binary-logs-二进制日志"><a href="#2-binlog-binary-logs-二进制日志" class="headerlink" title="2. binlog(binary logs):二进制日志 *****"></a>2. binlog(binary logs):二进制日志 *****</h1><h2 id="2-1-作用"><a href="#2-1-作用" class="headerlink" title="2.1 作用"></a>2.1 作用</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">(1)备份恢复必须依赖二进制日志</span><br><span class="line">(2)主从环境必须依赖二进制日志</span><br></pre></td></tr></table></figure>

<h2 id="2-2-binlog配置-5-7必须加server-id"><a href="#2-2-binlog配置-5-7必须加server-id" class="headerlink" title="2.2 binlog配置 (5.7必须加server_id)"></a>2.2 binlog配置 (5.7必须加server_id)</h2><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">注意：<span class="selector-tag">MySQL</span>默认是没有开启二进制日志的。</span><br><span class="line">基础参数查看:</span><br><span class="line">开关:</span><br><span class="line"><span class="selector-attr">[(none)]</span>&gt;<span class="selector-tag">select</span> @<span class="keyword">@log_bin</span>;</span><br><span class="line">日志路径及名字</span><br><span class="line"><span class="selector-attr">[(none)]</span>&gt;<span class="selector-tag">select</span> @<span class="keyword">@log_bin_basename</span>;</span><br><span class="line">服务<span class="selector-tag">ID</span>号:</span><br><span class="line"><span class="selector-attr">[(none)]</span>&gt;<span class="selector-tag">select</span> @<span class="keyword">@server_id</span>;</span><br><span class="line">二进制日志格式:</span><br><span class="line"><span class="selector-attr">[(none)]</span>&gt;<span class="selector-tag">select</span> @<span class="keyword">@binlog_format</span>;</span><br><span class="line">双一标准之二:</span><br><span class="line"><span class="selector-attr">[(none)]</span>&gt;<span class="selector-tag">select</span> @<span class="keyword">@sync_binlog</span>;</span><br></pre></td></tr></table></figure>

<h3 id="2-2-1-创建日志目录"><a href="#2-2-1-创建日志目录" class="headerlink" title="2.2.1 创建日志目录"></a>2.2.1 创建日志目录</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mkdir /<span class="keyword">data</span>/binlog</span><br><span class="line">chown -R mysql.mysql /<span class="keyword">data</span>/binlog</span><br></pre></td></tr></table></figure>

<h3 id="2-2-2-修改配置文件"><a href="#2-2-2-修改配置文件" class="headerlink" title="2.2.2 修改配置文件"></a>2.2.2 修改配置文件</h3><figure class="highlight jsx"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">vim /etc/my.cnf</span><br><span class="line">server_id=<span class="number">6</span>                                    -----&gt;<span class="number">5.6</span>中，单机可以不需要此参数              </span><br><span class="line">log_bin=<span class="regexp">/data/</span>binlog/mysql-bin</span><br><span class="line">binlog_format=row</span><br></pre></td></tr></table></figure>

<h3 id="2-2-3-重启数据库生效"><a href="#2-2-3-重启数据库生效" class="headerlink" title="2.2.3 重启数据库生效"></a>2.2.3 重启数据库生效</h3><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[<span class="meta">root@db01 mysql</span>]<span class="meta"># /etc/init.d/mysqld restart</span></span><br></pre></td></tr></table></figure>

<h3 id="2-2-4-参数说明"><a href="#2-2-4-参数说明" class="headerlink" title="2.2.4 参数说明"></a>2.2.4 参数说明</h3><figure class="highlight jsx"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">server_id=<span class="number">3306</span> </span><br><span class="line">主要是在主从复制过程中必须要加的,但是在<span class="number">5.7</span>版本中,要用以下参数(log_bin),开启binlog日志,即使是单机也是必加的</span><br><span class="line">log_bin=<span class="regexp">/data/</span>binlog/mysql-bin</span><br><span class="line">(<span class="number">1</span>)开启二进制日志功能</span><br><span class="line">(<span class="number">2</span>)设置二进制日志目录及名称前缀</span><br><span class="line">binlog_format=row</span><br><span class="line">binlog的记录格式??</span><br></pre></td></tr></table></figure>

<h2 id="2-3-binlog记录了什么"><a href="#2-3-binlog记录了什么" class="headerlink" title="2.3 binlog记录了什么?"></a>2.3 binlog记录了什么?</h2><h3 id="2-3-0-引入"><a href="#2-3-0-引入" class="headerlink" title="2.3.0 引入"></a>2.3.0 引入</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。</span><br></pre></td></tr></table></figure>

<h3 id="2-3-1-记录SQL语句种类"><a href="#2-3-1-记录SQL语句种类" class="headerlink" title="2.3.1 记录SQL语句种类"></a>2.3.1 记录SQL语句种类</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">DDL ：原封不动的记录当前DDL(statement语句方式)。</span><br><span class="line">DCL ：原封不动的记录当前DCL(statement语句方式)。</span><br><span class="line">DML ：只记录已经提交的事务DML</span><br></pre></td></tr></table></figure>

<h3 id="2-3-2-DML三种记录方式"><a href="#2-3-2-DML三种记录方式" class="headerlink" title="2.3.2 DML三种记录方式"></a>2.3.2 DML三种记录方式</h3><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="selector-tag">binlog_format</span>（<span class="selector-tag">binlog</span>的记录格式）参数影响</span><br><span class="line">（1）<span class="selector-tag">statement</span>（5<span class="selector-class">.6</span>默认）<span class="selector-tag">SBR</span>(<span class="selector-tag">statement</span> <span class="selector-tag">based</span> <span class="selector-tag">replication</span>) ：语句模式原封不动的记录当前<span class="selector-tag">DML</span>。</span><br><span class="line">（2）<span class="selector-tag">ROW</span>(5<span class="selector-class">.7</span> 默认值) <span class="selector-tag">RBR</span>(<span class="selector-tag">ROW</span> <span class="selector-tag">based</span> <span class="selector-tag">replication</span>) ：记录数据行的变化(用户看不懂，需要工具分析)</span><br><span class="line">（3）<span class="selector-tag">mixed</span>（混合）<span class="selector-tag">MBR</span>(<span class="selector-tag">mixed</span> <span class="selector-tag">based</span> <span class="selector-tag">replication</span>)模式  ：以上两种模式的混合</span><br></pre></td></tr></table></figure>

<h3 id="2-3-3-面试题"><a href="#2-3-3-面试题" class="headerlink" title="2.3.3 面试题"></a>2.3.3 面试题</h3><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">SBR与RBR模式的对比</span><br><span class="line">STATEMENT：可读性较高，日志量少，但是不够严谨</span><br><span class="line">ROW      ：可读性很低，日志量大，足够严谨</span><br><span class="line">update t1 <span class="keyword">set</span> xxx=xxx <span class="keyword">where</span> id&gt;<span class="number">1000</span>   ? --&gt;一共<span class="number">500</span>w行，row模式怎么记录的日志</span><br><span class="line">为什么row模式严谨？</span><br><span class="line">id  name    intime</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">1</span>,<span class="string">'zs'</span>,now(</span>))</span></span><br><span class="line"><span class="function">我们建议使用：row记录模式</span></span><br></pre></td></tr></table></figure>

<h2 id="2-4-event（事件）是什么"><a href="#2-4-event（事件）是什么" class="headerlink" title="2.4 event（事件）是什么?"></a>2.4 event（事件）是什么?</h2><h3 id="2-4-1-事件的简介"><a href="#2-4-1-事件的简介" class="headerlink" title="2.4.1 事件的简介"></a>2.4.1 事件的简介</h3><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">二进制日志的最小记录单元</span><br><span class="line">对于DDL,DCL,一个语句就是一个<span class="keyword">event</span></span><br><span class="line">对于DML语句来讲:只记录已提交的事务。</span><br><span class="line">例如以下列子,就被分为了<span class="number">4</span>个<span class="keyword">event</span></span><br><span class="line">begin;      <span class="number">120</span>  - <span class="number">340</span></span><br><span class="line">DML1        <span class="number">340</span>  - <span class="number">460</span></span><br><span class="line">DML2        <span class="number">460</span>  - <span class="number">550</span></span><br><span class="line">commit;     <span class="number">550</span>  - <span class="number">760</span></span><br></pre></td></tr></table></figure>

<h3 id="2-4-2-event的组成"><a href="#2-4-2-event的组成" class="headerlink" title="2.4.2 event的组成"></a>2.4.2 event的组成</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">三部分构成:</span><br><span class="line">(1) 事件的开始标识</span><br><span class="line">(2) 事件内容</span><br><span class="line">(3) 事件的结束标识</span><br><span class="line">Position:</span><br><span class="line">开始标识: at 194</span><br><span class="line">结束标识: end_log_pos 254</span><br><span class="line">194? 254?</span><br><span class="line">某个事件在binlog中的相对位置号</span><br><span class="line">位置号的作用是什么？</span><br><span class="line">为了方便我们截取事件</span><br></pre></td></tr></table></figure>

<h2 id="2-5-日志文件查看"><a href="#2-5-日志文件查看" class="headerlink" title="2.5 日志文件查看"></a>2.5 日志文件查看</h2><h3 id="2-5-1-查看日志的开启情况"><a href="#2-5-1-查看日志的开启情况" class="headerlink" title="2.5.1 查看日志的开启情况"></a>2.5.1 查看日志的开启情况</h3><p>log_bin参数设置的路径,可以找到二进制日志</p>
<figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line">Master [(none)]&gt;show variables like <span class="string">'%log_bin%'</span>;</span><br><span class="line">+---------------------------------+------------------------------+</span><br><span class="line"><span class="params">| Variable_name                   |</span> Value                        <span class="params">|</span></span><br><span class="line"><span class="params">+---------------------------------+------------------------------+</span></span><br><span class="line"><span class="params">|</span> log_bin                         <span class="params">| ON                           |</span></span><br><span class="line"><span class="params">| log_bin_basename                |</span> /data/binlog/mysql-bin       <span class="params">|</span></span><br><span class="line"><span class="params">|</span> log_bin_index                   <span class="params">| /data/binlog/mysql-bin.index |</span></span><br><span class="line"><span class="params">| log_bin_trust_function_creators |</span> OFF                          <span class="params">|</span></span><br><span class="line"><span class="params">|</span> log_bin_use_v1_row_events       <span class="params">| OFF                          |</span></span><br><span class="line"><span class="params">| sql_log_bin                     |</span> ON                           <span class="params">|</span></span><br><span class="line"><span class="params">+---------------------------------+------------------------------+</span></span><br><span class="line"><span class="params">6 rows <span class="keyword">in</span> set (0.01 sec)</span></span><br></pre></td></tr></table></figure>

<h3 id="2-5-2-查看一共多少个binlog"><a href="#2-5-2-查看一共多少个binlog" class="headerlink" title="2.5.2 查看一共多少个binlog"></a>2.5.2 查看一共多少个binlog</h3><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">Master [(none)]&gt;show binary logs;</span><br><span class="line">+------------------+-----------+</span><br><span class="line"><span class="params">| Log_name         |</span> File_size <span class="params">|</span></span><br><span class="line"><span class="params">+------------------+-----------+</span></span><br><span class="line"><span class="params">|</span> mysql-bin.<span class="number">000001</span> <span class="params">|       154 |</span></span><br><span class="line">+------------------+-----------+</span><br><span class="line"><span class="number">1</span> row <span class="keyword">in</span> set (<span class="number">0</span>.<span class="number">01</span> sec)</span><br><span class="line"></span><br><span class="line">Master [(none)]&gt;flush logs;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">03</span> sec)</span><br><span class="line"></span><br><span class="line">Master [(none)]&gt;flush logs;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">01</span> sec)</span><br><span class="line"></span><br><span class="line">Master [(none)]&gt;show binary logs;</span><br><span class="line">+------------------+-----------+</span><br><span class="line"><span class="params">| Log_name         |</span> File_size <span class="params">|</span></span><br><span class="line"><span class="params">+------------------+-----------+</span></span><br><span class="line"><span class="params">|</span> mysql-bin.<span class="number">000001</span> <span class="params">|       201 |</span></span><br><span class="line"><span class="params">| mysql-bin.000002 |</span>       <span class="number">201</span> <span class="params">|</span></span><br><span class="line"><span class="params">|</span> mysql-bin.<span class="number">000003</span> <span class="params">|       154 |</span></span><br><span class="line">+------------------+-----------+</span><br><span class="line"><span class="number">3</span> rows <span class="keyword">in</span> set (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [(none)]&gt;</span><br></pre></td></tr></table></figure>

<h3 id="2-5-3-查看mysql正在使用的日志文件"><a href="#2-5-3-查看mysql正在使用的日志文件" class="headerlink" title="2.5.3 查看mysql正在使用的日志文件"></a>2.5.3 查看mysql正在使用的日志文件</h3><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">Master [(none)]&gt;show master status;</span><br><span class="line">+------------------+----------+--------------+------------------+-------------------+</span><br><span class="line"><span class="params">| File             |</span> Position <span class="params">| Binlog_Do_DB |</span> Binlog_Ignore_DB <span class="params">| Executed_Gtid_Set |</span></span><br><span class="line">+------------------+----------+--------------+------------------+-------------------+</span><br><span class="line"><span class="params">| mysql-bin.000003 |</span>      <span class="number">154</span> <span class="params">|              |</span>                  <span class="params">|                   |</span></span><br><span class="line">+------------------+----------+--------------+------------------+-------------------+</span><br><span class="line">Master [(none)]&gt;</span><br></pre></td></tr></table></figure>

<p>file：当前MySQL正在使用的文件名<br> Position：最后一个事件的结束位置号</p>
<h2 id="2-6-日志内容查看"><a href="#2-6-日志内容查看" class="headerlink" title="2.6 日志内容查看"></a>2.6 日志内容查看</h2><h3 id="2-6-1-event查看"><a href="#2-6-1-event查看" class="headerlink" title="2.6.1 event查看"></a>2.6.1 event查看</h3><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">Master [binlog]&gt;show binlog events <span class="keyword">in</span> <span class="string">'mysql-bin.000003'</span>;</span><br><span class="line">+------------------+-----+----------------+-----------+-------------+----------------------------------------+</span><br><span class="line"><span class="params">| Log_name         |</span> Pos <span class="params">| Event_type     |</span> Server_id <span class="params">| End_log_pos |</span> Info                                   <span class="params">|</span></span><br><span class="line"><span class="params">+------------------+-----+----------------+-----------+-------------+----------------------------------------+</span></span><br><span class="line"><span class="params">|</span> mysql-bin.<span class="number">000003</span> <span class="params">|   4 |</span> Format_desc    <span class="params">|         6 |</span>         <span class="number">123</span> <span class="params">| Server ver: 5.7.20-log, Binlog ver: 4  |</span></span><br><span class="line"><span class="params">| mysql-bin.000003 |</span> <span class="number">123</span> <span class="params">| Previous_gtids |</span>         <span class="number">6</span> <span class="params">|         154 |</span>                                        <span class="params">|</span></span><br><span class="line"><span class="params">|</span> mysql-bin.<span class="number">000003</span> <span class="params">| 154 |</span> Anonymous_Gtid <span class="params">|         6 |</span>         <span class="number">219</span> <span class="params">| SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |</span></span><br><span class="line"><span class="params">| mysql-bin.000003 |</span> <span class="number">219</span> <span class="params">| Query          |</span>         <span class="number">6</span> <span class="params">|         319 |</span> create database binlog                 <span class="params">|</span></span><br><span class="line"><span class="params">|</span> mysql-bin.<span class="number">000003</span> <span class="params">| 319 |</span> Anonymous_Gtid <span class="params">|         6 |</span>         <span class="number">384</span> <span class="params">| SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   |</span></span><br><span class="line"><span class="params">| mysql-bin.000003 |</span> <span class="number">384</span> <span class="params">| Query          |</span>         <span class="number">6</span> <span class="params">|         486 |</span> use <span class="string">`binlog`</span>; create table t1 (id int) <span class="params">|</span></span><br><span class="line"><span class="params">+------------------+-----+----------------+-----------+-------------+----------------------------------------+</span></span><br><span class="line"><span class="params"></span></span><br><span class="line"><span class="params">Log_name：binlog文件名</span></span><br><span class="line"><span class="params">Pos：开始的position    *****</span></span><br><span class="line"><span class="params">Event_type：事件类型</span></span><br><span class="line"><span class="params">Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息</span></span><br><span class="line"><span class="params">Server_id：mysql服务号标识</span></span><br><span class="line"><span class="params">End_log_pos：事件的结束位置号 *****</span></span><br><span class="line"><span class="params">Info：事件内容*****</span></span><br><span class="line"><span class="params">补充:</span></span><br><span class="line"><span class="params">SHOW BINLOG EVENTS</span></span><br><span class="line"><span class="params">   [IN 'log_name']</span></span><br><span class="line"><span class="params">   [FROM pos]</span></span><br><span class="line"><span class="params">   [LIMIT [offset,] row_count]</span></span><br><span class="line"><span class="params">[root@db01 binlog]# mysql -e "show binlog events <span class="keyword">in</span> 'mysql-bin.000004'" |</span>grep drop</span><br></pre></td></tr></table></figure>

<h3 id="2-6-2-binlog文件内容详细查看"><a href="#2-6-2-binlog文件内容详细查看" class="headerlink" title="2.6.2 binlog文件内容详细查看"></a>2.6.2 binlog文件内容详细查看</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">mysqlbinlog /<span class="keyword">data</span>/mysql/mysql-bin<span class="number">.000006</span></span><br><span class="line">mysqlbinlog --base64-output=decode-rows -vvv /<span class="keyword">data</span>/binlog/mysql-bin<span class="number">.000003</span></span><br><span class="line">mysqlbinlog  -d binlog /<span class="keyword">data</span>/binlog/mysql-bin<span class="number">.000003</span></span><br><span class="line">[<span class="symbol">root@</span>db01 binlog]# mysqlbinlog --start-datetime=<span class="string">'2019-05-06 17:00:00'</span> --stop-datetime=<span class="string">'2019-05-06 17:01:00'</span>  /<span class="keyword">data</span>/binlog/mysql-bin<span class="number">.000004</span></span><br></pre></td></tr></table></figure>

<h2 id="2-7-基于Position号进行日志截取"><a href="#2-7-基于Position号进行日志截取" class="headerlink" title="2.7 基于Position号进行日志截取"></a>2.7 基于Position号进行日志截取</h2><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br></pre></td><td class="code"><pre><span class="line">核心就是找截取的起点和终点</span><br><span class="line">--start-position=<span class="number">321</span></span><br><span class="line">--stop-position=<span class="number">513</span></span><br><span class="line"> mysqlbinlog --start-position=<span class="number">219</span> --stop-position=<span class="number">1347</span> /data/binlog/mysql-bin<span class="number">.000003</span> &gt;/tmp/bin.sql</span><br><span class="line"></span><br><span class="line">案例: 使用binlog日志进行数据恢复</span><br><span class="line">模拟:</span><br><span class="line"><span class="number">1.</span> </span><br><span class="line">[<span class="meta">(none)</span>]&gt;create database binlog charset utf8;</span><br><span class="line"><span class="number">2.</span> </span><br><span class="line">[<span class="meta">(none)</span>]&gt;use binlog;</span><br><span class="line">[<span class="meta">binlog</span>]&gt;<span class="function">create table <span class="title">t1</span>(<span class="params">id <span class="keyword">int</span></span>)</span>;</span><br><span class="line"><span class="number">3.</span> </span><br><span class="line">[<span class="meta">binlog</span>]&gt;<span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">1</span></span>)</span>;</span><br><span class="line">[<span class="meta">binlog</span>]&gt;commit;</span><br><span class="line">[<span class="meta">binlog</span>]&gt;<span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">2</span></span>)</span>;</span><br><span class="line">[<span class="meta">binlog</span>]&gt;commit;</span><br><span class="line">[<span class="meta">binlog</span>]&gt;<span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">3</span></span>)</span>;</span><br><span class="line">[<span class="meta">binlog</span>]&gt;commit;</span><br><span class="line"><span class="number">4.</span> </span><br><span class="line">[<span class="meta">binlog</span>]&gt;drop database binlog;</span><br><span class="line">恢复:</span><br><span class="line">[<span class="meta">(none)</span>]&gt;show master status ;</span><br><span class="line">[<span class="meta">(none)</span>]&gt;show binlog events <span class="keyword">in</span> <span class="string">'mysql-bin.000004'</span>;</span><br><span class="line">[<span class="meta">root@db01 binlog</span>]<span class="meta"># mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 &gt;/tmp/bin.sql</span></span><br><span class="line">[<span class="meta">(none)</span>]&gt;<span class="keyword">set</span> sql_Log_bin=<span class="number">0</span>;</span><br><span class="line">[<span class="meta">(none)</span>]&gt;source /tmp/bin.sql</span><br><span class="line"></span><br><span class="line">面试案例:</span><br><span class="line"><span class="number">1.</span> 备份策略每天全备,有全量的二进制日志</span><br><span class="line"><span class="number">2.</span>业务中一共<span class="number">10</span>个库,其中一个被误drop了</span><br><span class="line"><span class="number">3.</span> 需要在其他<span class="number">9</span>个库正常工作过程中进行数据恢复</span><br></pre></td></tr></table></figure>

<h2 id="2-8-binlog日志的GTID新特性"><a href="#2-8-binlog日志的GTID新特性" class="headerlink" title="2.8 binlog日志的GTID新特性"></a>2.8 binlog日志的GTID新特性</h2><h3 id="2-8-1-GTID-介绍"><a href="#2-8-1-GTID-介绍" class="headerlink" title="2.8.1 GTID 介绍"></a>2.8.1 GTID 介绍</h3><figure class="highlight css"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">5<span class="selector-class">.6</span> 版本新加的特性,5<span class="selector-class">.7</span>中做了加强</span><br><span class="line">5<span class="selector-class">.6</span> 中不开启,没有这个功能.</span><br><span class="line">5<span class="selector-class">.7</span> 中的<span class="selector-tag">GTID</span>,即使不开也会有自动生成</span><br><span class="line"><span class="selector-tag">SET</span> @<span class="keyword">@SESSION</span>.GTID_NEXT= <span class="string">'ANONYMOUS'</span></span><br></pre></td></tr></table></figure>

<h3 id="2-8-2-GTID-Global-Transaction-ID"><a href="#2-8-2-GTID-Global-Transaction-ID" class="headerlink" title="2.8.2. GTID(Global Transaction ID)"></a>2.8.2. GTID(Global Transaction ID)</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">是对于一个已提交事务的编号，并且是一个全局唯一的编号。</span><br><span class="line">它的官方定义如下：</span><br><span class="line"></span><br><span class="line">GTID &#x3D; source_id ：transaction_id</span><br><span class="line">7E11FA47-31CA-19E1-9E56-C43AA21293967:29</span><br></pre></td></tr></table></figure>

<p>重要参数介绍：</p>
<figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br></pre></td><td class="code"><pre><span class="line">vim /etc/my.cnf</span><br><span class="line">gtid-mode=on</span><br><span class="line">enforce-gtid-consistency=<span class="literal">true</span></span><br><span class="line">systemctl restart mysqld</span><br><span class="line"></span><br><span class="line">Master [(none)]&gt;create database gtid charset utf8;</span><br><span class="line">Query OK, <span class="number">1</span> row affected (<span class="number">0</span>.<span class="number">01</span> sec)</span><br><span class="line"></span><br><span class="line">Master [(none)]&gt;show master status ;</span><br><span class="line">+------------------+----------+--------------+------------------+----------------------------------------+</span><br><span class="line"><span class="params">| File             |</span> Position <span class="params">| Binlog_Do_DB |</span> Binlog_Ignore_DB <span class="params">| Executed_Gtid_Set                      |</span></span><br><span class="line">+------------------+----------+--------------+------------------+----------------------------------------+</span><br><span class="line"><span class="params">| mysql-bin.000004 |</span>      <span class="number">326</span> <span class="params">|              |</span>                  <span class="params">| dff98809-55c3-11e9-a58b-000c2928f5dd:1 |</span></span><br><span class="line">+------------------+----------+--------------+------------------+----------------------------------------+</span><br><span class="line"><span class="number">1</span> row <span class="keyword">in</span> set (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [(none)]&gt;use gtid</span><br><span class="line">Database changed</span><br><span class="line">Master [gtid]&gt;create table t1 (id int);</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">01</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;show master status ;</span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="params">| File             |</span> Position <span class="params">| Binlog_Do_DB |</span> Binlog_Ignore_DB <span class="params">| Executed_Gtid_Set                        |</span></span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="params">| mysql-bin.000004 |</span>      <span class="number">489</span> <span class="params">|              |</span>                  <span class="params">| dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 |</span></span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="number">1</span> row <span class="keyword">in</span> set (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;create table t2 (id int);</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">01</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;create table t3 (id int);</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">02</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;show master status ;</span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="params">| File             |</span> Position <span class="params">| Binlog_Do_DB |</span> Binlog_Ignore_DB <span class="params">| Executed_Gtid_Set                        |</span></span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="params">| mysql-bin.000004 |</span>      <span class="number">815</span> <span class="params">|              |</span>                  <span class="params">| dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 |</span></span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="number">1</span> row <span class="keyword">in</span> set (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;<span class="keyword">begin</span>;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;insert into t1 values(<span class="number">1</span>);</span><br><span class="line">Query OK, <span class="number">1</span> row affected (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;commit;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;show master status ;</span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="params">| File             |</span> Position <span class="params">| Binlog_Do_DB |</span> Binlog_Ignore_DB <span class="params">| Executed_Gtid_Set                        |</span></span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="params">| mysql-bin.000004 |</span>     <span class="number">1068</span> <span class="params">|              |</span>                  <span class="params">| dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 |</span></span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="number">1</span> row <span class="keyword">in</span> set (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;<span class="keyword">begin</span>;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;insert into t2 values(<span class="number">1</span>);</span><br><span class="line">Query OK, <span class="number">1</span> row affected (<span class="number">0</span>.<span class="number">00</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;commit;</span><br><span class="line">Query OK, <span class="number">0</span> rows affected (<span class="number">0</span>.<span class="number">01</span> sec)</span><br><span class="line"></span><br><span class="line">Master [gtid]&gt;show master status ;</span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="params">| File             |</span> Position <span class="params">| Binlog_Do_DB |</span> Binlog_Ignore_DB <span class="params">| Executed_Gtid_Set                        |</span></span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="params">| mysql-bin.000004 |</span>     <span class="number">1321</span> <span class="params">|              |</span>                  <span class="params">| dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 |</span></span><br><span class="line">+------------------+----------+--------------+------------------+------------------------------------------+</span><br><span class="line"><span class="number">1</span> row <span class="keyword">in</span> set (<span class="number">0</span>.<span class="number">00</span> sec)</span><br></pre></td></tr></table></figure>

<h3 id="2-8-3-基于GTID进行查看binlog"><a href="#2-8-3-基于GTID进行查看binlog" class="headerlink" title="2.8.3. 基于GTID进行查看binlog"></a>2.8.3. 基于GTID进行查看binlog</h3><figure class="highlight php"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">具备GTID后,截取查看某些事务日志:</span><br><span class="line">--<span class="keyword">include</span>-gtids</span><br><span class="line">--exclude-gtids</span><br><span class="line">mysqlbinlog --<span class="keyword">include</span>-gtids=<span class="string">'dff98809-55c3-11e9-a58b-000c2928f5dd:1-6'</span> --exclude-gtids=<span class="string">'dff98809-55c3-11e9-a58b-000c2928f5dd:4'</span>  /data/binlog/mysql-bin<span class="number">.000004</span></span><br></pre></td></tr></table></figure>

<h3 id="2-8-4-GTID的幂等性"><a href="#2-8-4-GTID的幂等性" class="headerlink" title="2.8.4  GTID的幂等性"></a>2.8.4  GTID的幂等性</h3><figure class="highlight kotlin"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line">开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了</span><br><span class="line">就想恢复?怎么办?</span><br><span class="line">--skip-gtids</span><br><span class="line">mysqlbinlog --include-gtids=<span class="string">'3ca79ab5-3e4d-11e9-a709-000c293b577e:4'</span> /<span class="keyword">data</span>/binlog/mysql-bin<span class="number">.000004</span> /<span class="keyword">data</span>/binlog/mysql-bin<span class="number">.000004</span></span><br><span class="line"><span class="keyword">set</span> sql_log_bin=<span class="number">0</span>;</span><br><span class="line">source /tmp/binlog.sql</span><br><span class="line"><span class="keyword">set</span> sql_log_bin=<span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<h2 id="2-9-使用二进制日志恢复数据案例"><a href="#2-9-使用二进制日志恢复数据案例" class="headerlink" title="2.9 使用二进制日志恢复数据案例"></a>2.9 使用二进制日志恢复数据案例</h2><h3 id="2-9-1-故障环境介绍"><a href="#2-9-1-故障环境介绍" class="headerlink" title="2.9.1 故障环境介绍"></a>2.9.1 故障环境介绍</h3><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line">创建了一个库  db, 导入了表t1 ,t1表中录入了很多数据</span><br><span class="line">一个开发人员,drop database db;</span><br><span class="line">没有备份,日志都在.怎么恢复?</span><br><span class="line">思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)</span><br><span class="line">故障案例模拟:</span><br><span class="line">(<span class="number">0</span>) drop database <span class="keyword">if</span> exists db ;</span><br><span class="line">(<span class="number">1</span>) create database db charset utf8;     </span><br><span class="line">(<span class="number">2</span>) use db;</span><br><span class="line">(<span class="number">3</span>) <span class="function">create table <span class="title">t1</span> (<span class="params">id <span class="keyword">int</span></span>)</span>;</span><br><span class="line">(<span class="number">4</span>) <span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">1</span></span>),(<span class="params"><span class="number">2</span></span>),(<span class="params"><span class="number">3</span></span>)</span>;</span><br><span class="line">(<span class="number">5</span>) <span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">4</span></span>),(<span class="params"><span class="number">5</span></span>),(<span class="params"><span class="number">6</span></span>)</span>;</span><br><span class="line">(<span class="number">6</span>) commit</span><br><span class="line">(<span class="number">7</span>) update t1 <span class="keyword">set</span> id=<span class="number">30</span> <span class="keyword">where</span> id=<span class="number">3</span>;</span><br><span class="line">(<span class="number">8</span>) commit;</span><br><span class="line">(<span class="number">9</span>) delete <span class="keyword">from</span> t1 <span class="keyword">where</span> id=<span class="number">4</span>;</span><br><span class="line">(<span class="number">10</span>)commit;</span><br><span class="line">(<span class="number">11</span>)<span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">7</span></span>),(<span class="params"><span class="number">8</span></span>),(<span class="params"><span class="number">9</span></span>)</span>;</span><br><span class="line">(<span class="number">12</span>)commit;</span><br><span class="line">(<span class="number">13</span>)drop database db;</span><br><span class="line">========================</span><br><span class="line">drop database <span class="keyword">if</span> exists db ;</span><br><span class="line">create database db charset utf8; </span><br><span class="line">use db;</span><br><span class="line"><span class="function">create table <span class="title">t1</span> (<span class="params">id <span class="keyword">int</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">1</span></span>),(<span class="params"><span class="number">2</span></span>),(<span class="params"><span class="number">3</span></span>)</span>;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">4</span></span>),(<span class="params"><span class="number">5</span></span>),(<span class="params"><span class="number">6</span></span>)</span>;</span><br><span class="line">commit;</span><br><span class="line">update t1 <span class="keyword">set</span> id=<span class="number">30</span> <span class="keyword">where</span> id=<span class="number">3</span>;</span><br><span class="line">commit;</span><br><span class="line">delete <span class="keyword">from</span> t1 <span class="keyword">where</span> id=<span class="number">4</span>;</span><br><span class="line">commit;</span><br><span class="line"><span class="function">insert <span class="keyword">into</span> t1 <span class="title">values</span>(<span class="params"><span class="number">7</span></span>),(<span class="params"><span class="number">8</span></span>),(<span class="params"><span class="number">9</span></span>)</span>;</span><br><span class="line">commit;</span><br><span class="line">drop database db;</span><br><span class="line">=======</span><br><span class="line">运行以上语句，模拟故障场景</span><br><span class="line">需求：将数据库恢复到以下状态（提示第<span class="number">9</span>步和第<span class="number">13</span>步是误操作，其他都是正常操作）</span><br></pre></td></tr></table></figure>

<h3 id="2-9-2-恢复过程-无GTID时的恢复"><a href="#2-9-2-恢复过程-无GTID时的恢复" class="headerlink" title="2.9.2 恢复过程(无GTID时的恢复)"></a>2.9.2 恢复过程(无GTID时的恢复)</h3><ol>
<li>查看当前使用的 binlog文件</li>
</ol>
<figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line">oldguo [db]&gt;show master status ;</span><br><span class="line">+------------------+----------+--------------+------------------+-------------------+</span><br><span class="line"></span><br><span class="line">| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |</span><br><span class="line"></span><br><span class="line">+------------------+----------+--------------+------------------+-------------------+</span><br><span class="line"></span><br><span class="line">| mysql-bin<span class="number">.000006</span> |    <span class="number">1873</span> |              |                  |                  |</span><br><span class="line"></span><br><span class="line">+------------------+----------+--------------+------------------+-------------------+</span><br><span class="line"></span><br><span class="line"><span class="number">2.</span>查看事件：</span><br><span class="line"></span><br><span class="line">第一段：</span><br><span class="line">| mysql-bin<span class="number">.000006</span> |  <span class="number">813</span> | Query      |        <span class="number">1</span> |        <span class="number">907</span> | use `db`; <span class="function">create table <span class="title">t1</span> (<span class="params">id <span class="keyword">int</span></span>)                  |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 |  907 | Query      |        1 |        977 | BEGIN                                              |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 |  977 | Table_map  |        1 |        1020 | table_id: 77 (<span class="params">db.t1</span>)                                |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 | 1020 | Write_rows  |        1 |        1070 | table_id: 77 flags: STMT_END_F                      |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 | 1070 | Table_map  |        1 |        1113 | table_id: 77 (<span class="params">db.t1</span>)                                |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 | 1113 | Write_rows  |        1 |        1163 | table_id: 77 flags: STMT_END_F                      |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 | 1163 | Xid        |        1 |        1194 | COMMIT <span class="comment">/* xid=74 */</span>                                |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 | 1194 | Query      |        1 |        1264 | BEGIN                                              |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 | 1264 | Table_map  |        1 |        1307 | table_id: 77 (<span class="params">db.t1</span>)                                |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 | 1307 | Update_rows |        1 |        1353 | table_id: 77 flags: STMT_END_F                      |</span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">| mysql-bin.000006 | 1353 | Xid        |        1 |        1384 | COMMIT <span class="comment">/* xid=77 */</span>   </span></span><br><span class="line"><span class="function"></span></span><br><span class="line"><span class="function">mysqlbinlog --start-position</span>=<span class="number">813</span> --stop-position=<span class="number">1384</span> /data/mysql/mysql-bin<span class="number">.000006</span> &gt;/tmp/bin1.sql</span><br></pre></td></tr></table></figure>

<p>第二段：</p>
<figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="params">| mysql-bin.000006 |</span> <span class="number">1568</span> <span class="params">| Query      |</span>        <span class="number">1</span> <span class="params">|        1638 |</span> <span class="keyword">BEGIN</span>                                              <span class="params">|</span></span><br><span class="line"><span class="params"></span></span><br><span class="line"><span class="params">|</span> mysql-bin.<span class="number">000006</span> <span class="params">| 1638 |</span> Table_map  <span class="params">|        1 |</span>        <span class="number">1681</span> <span class="params">| table_id: 77 (db.t1)                                |</span></span><br><span class="line"></span><br><span class="line"><span class="params">| mysql-bin.000006 |</span> <span class="number">1681</span> <span class="params">| Write_rows  |</span>        <span class="number">1</span> <span class="params">|        1731 |</span> <span class="symbol">table_id:</span> <span class="number">77</span> <span class="symbol">flags:</span> STMT_END_F                      <span class="params">|</span></span><br><span class="line"><span class="params"></span></span><br><span class="line"><span class="params">|</span> mysql-bin.<span class="number">000006</span> <span class="params">| 1731 |</span> Xid        <span class="params">|        1 |</span>        <span class="number">1762</span> <span class="params">| COMMIT /* xid=81 */ </span></span><br><span class="line"><span class="params"></span></span><br><span class="line"><span class="params">mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 &gt;/tmp/bin2.sql</span></span><br></pre></td></tr></table></figure>

<p>3.恢复</p>
<figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line">set sql_log_bin=<span class="number">0</span>;</span><br><span class="line">source /tmp/bin1.sql</span><br><span class="line">source /tmp/bin2.sql</span><br><span class="line">set sql_log_bin=<span class="number">1</span>;</span><br><span class="line">oldguo [db]&gt;select * from t1;</span><br><span class="line"></span><br><span class="line">+------+</span><br><span class="line"></span><br><span class="line"><span class="params">| id  |</span></span><br><span class="line"></span><br><span class="line">+------+</span><br><span class="line"></span><br><span class="line"><span class="params">|    1 |</span></span><br><span class="line"></span><br><span class="line"><span class="params">|    2 |</span></span><br><span class="line"></span><br><span class="line"><span class="params">|  30 |</span></span><br><span class="line"></span><br><span class="line"><span class="params">|    4 |</span></span><br><span class="line"></span><br><span class="line"><span class="params">|    5 |</span></span><br><span class="line"></span><br><span class="line"><span class="params">|    6 |</span></span><br><span class="line"></span><br><span class="line"><span class="params">|    7 |</span></span><br><span class="line"></span><br><span class="line"><span class="params">|    8 |</span></span><br><span class="line"></span><br><span class="line"><span class="params">|    9 |</span></span><br></pre></td></tr></table></figure>

<h3 id="2-9-3-有GTID的恢复"><a href="#2-9-3-有GTID的恢复" class="headerlink" title="2.9.3 有GTID的恢复:"></a>2.9.3 有GTID的恢复:</h3><p>(1)截取</p>
<figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqlbinlog --skip-gtids --<span class="keyword">include</span>-gtids=<span class="string">'3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12'</span> mysql-bin.<span class="number">000004</span>&gt; <span class="regexp">/tmp/bin</span>.sql</span><br></pre></td></tr></table></figure>

<p>(2)恢复</p>
<figure class="highlight bash"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="built_in">set</span> sql_log_bin=0;</span><br><span class="line"><span class="built_in">source</span> /tmp/bin.sql</span><br></pre></td></tr></table></figure>

<h2 id="2-10二进制日志其他操作"><a href="#2-10二进制日志其他操作" class="headerlink" title="2.10二进制日志其他操作"></a>2.10二进制日志其他操作</h2><h3 id="2-10-1-自动清理日志"><a href="#2-10-1-自动清理日志" class="headerlink" title="2.10.1 自动清理日志"></a>2.10.1 自动清理日志</h3><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">show variables like <span class="string">'%expire%'</span>;</span><br><span class="line">expire_logs_days  <span class="number">0</span>   </span><br><span class="line">自动清理时间,是要按照全备周期+<span class="number">1</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> expire_logs_days=<span class="number">8</span>;</span><br><span class="line">永久生效:</span><br><span class="line">my.cnf</span><br><span class="line">expire_logs_days=<span class="number">15</span>;</span><br><span class="line">企业建议,至少保留两个全备周期+<span class="number">1</span>的binlog</span><br></pre></td></tr></table></figure>

<h3 id="2-10-2-手工清理"><a href="#2-10-2-手工清理" class="headerlink" title="2.10.2 手工清理"></a>2.10.2 手工清理</h3><figure class="highlight csharp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="function">PURGE BINARY LOGS BEFORE <span class="title">now</span>(<span class="params"></span>) - INTERVAL 3 day</span>;</span><br><span class="line">PURGE BINARY LOGS TO <span class="string">'mysql-bin.000010'</span>;</span><br><span class="line">注意:不要手工 rm binlog文件</span><br><span class="line"><span class="number">1.</span> my.cnf binlog关闭掉,启动数据库</span><br><span class="line"><span class="number">2.</span>把数据库关闭,开启binlog,启动数据库</span><br><span class="line">删除所有binlog,并从<span class="number">000001</span>开始重新记录日志</span><br></pre></td></tr></table></figure>

<p><strong>*reset master;     主从关系中，主库执行此操作，主从环境必崩</strong></p>
<h3 id="2-10-3-日志是怎么滚动"><a href="#2-10-3-日志是怎么滚动" class="headerlink" title="2.10.3 日志是怎么滚动"></a>2.10.3 日志是怎么滚动</h3><figure class="highlight ruby"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">flush logs; </span><br><span class="line">重启mysql也会自动滚动一个新的</span><br><span class="line">日志文件达到<span class="number">1</span>G大小(max_binlog_size)</span><br><span class="line"><span class="params">| max_binlog_size                          |</span> <span class="number">1073741824</span>     </span><br><span class="line">备份时,加入参数也可以自动滚动</span><br></pre></td></tr></table></figure>

<h1 id="3-slow-log-慢日志"><a href="#3-slow-log-慢日志" class="headerlink" title="3.slow_log 慢日志"></a>3.slow_log 慢日志</h1><h2 id="3-1-作用"><a href="#3-1-作用" class="headerlink" title="3.1 作用:"></a>3.1 作用:</h2><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">记录慢SQL语句的日志,定位低效SQL语句的工具日志</span><br></pre></td></tr></table></figure>

<h2 id="3-2-开启慢日志-默认没开启"><a href="#3-2-开启慢日志-默认没开启" class="headerlink" title="3.2 开启慢日志(默认没开启)"></a>3.2 开启慢日志(默认没开启)</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">开关:</span><br><span class="line">slow_query_log=<span class="number">1</span> </span><br><span class="line">文件位置及名字 </span><br><span class="line">slow_query_log_file=/data/mysql/slow.<span class="built_in">log</span></span><br><span class="line">设定慢查询时间:</span><br><span class="line">long_query_time=<span class="number">0.1</span></span><br><span class="line">没走索引的语句也记录:</span><br><span class="line">log_queries_not_using_indexes</span><br><span class="line">vim /etc/my.cnf</span><br><span class="line">slow_query_log=<span class="number">1</span> </span><br><span class="line">slow_query_log_file=/data/mysql/slow.<span class="built_in">log</span></span><br><span class="line">long_query_time=<span class="number">0.1</span></span><br><span class="line">log_queries_not_using_indexes</span><br><span class="line">systemctl restart mysqld</span><br></pre></td></tr></table></figure>

<h2 id="3-3-mysqldumpslow-分析慢日志"><a href="#3-3-mysqldumpslow-分析慢日志" class="headerlink" title="3.3 mysqldumpslow 分析慢日志"></a>3.3 mysqldumpslow 分析慢日志</h2><figure class="highlight cpp"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">mysqldumpslow -s c -t <span class="number">10</span> /data/mysql/slow.<span class="built_in">log</span></span><br><span class="line"></span><br><span class="line"># 第三方工具(自己扩展)</span><br><span class="line">https:<span class="comment">//www.percona.com/downloads/percona-toolkit/LATEST/</span></span><br><span class="line">yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5</span><br><span class="line">toolkit工具包中的命令:</span><br><span class="line">./pt-query-diagest  /data/mysql/slow.<span class="built_in">log</span></span><br><span class="line">Anemometer基于pt-query-digest将MySQL慢查询可视化</span><br></pre></td></tr></table></figure>



<p>作者：wwwoldguocom<br>链接：<a href="https://www.jianshu.com/p/00c54d2832ed" target="_blank" rel="noopener">https://www.jianshu.com/p/00c54d2832ed</a><br>来源：简书<br>著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。</p>
</section>
    <!-- Tags START -->
    
    <!-- Tags END -->
    <!-- NAV START -->
    
  <div class="nav-container">
    <!-- reverse left and right to put prev and next in a more logic postition -->
    
      <a class="nav-left" href="/db/Redis%E7%B3%BB%E5%88%97/00-Redis%E7%B3%BB%E5%88%97%E4%B9%8B-Redis%E4%BB%8B%E7%BB%8D%E5%AE%89%E8%A3%85%E9%85%8D%E7%BD%AE/">
        <span class="nav-arrow">← </span>
        
          db/Redis系列/00-Redis系列之-Redis介绍安装配置
        
      </a>
    
    
      <a class="nav-right" href="/db/MySQL%E7%B3%BB%E5%88%97/12-MySQL%E7%B3%BB%E5%88%97%E4%B9%8B-MyCat/">
        
          db/MySQL系列/12-MySQL系列之-MyCat
        
        <span class="nav-arrow"> →</span>
      </a>
    
  </div>

    <!-- NAV END -->
    <!-- 打赏 START -->
    
      <div class="money-like">
        <div class="reward-btn">
          赏
          <span class="money-code">
            <span class="alipay-code">
              <div class="code-image"></div>
              <b>使用支付宝打赏</b>
            </span>
            <span class="wechat-code">
              <div class="code-image"></div>
              <b>使用微信打赏</b>
            </span>
          </span>
        </div>
        <p class="notice">点击上方按钮,请我喝杯咖啡！</p>
      </div>
    
    <!-- 打赏 END -->
    <!-- 二维码 START -->
    
      <div class="qrcode">
        <canvas id="share-qrcode"></canvas>
        <p class="notice">扫描二维码，分享此文章</p>
      </div>
    
    <!-- 二维码 END -->
    
      <!-- Gitment START -->
      <div id="comments"></div>
      <!-- Gitment END -->
    
  </article>
  <!-- Article END -->
  <!-- Catalog START -->
  
    <aside class="catalog-container">
  <div class="toc-main">
  <!-- 不蒜子统计 -->
    <strong class="toc-title">目录</strong>
    
      <ol class="toc-nav"><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#1-错误日志-log-error"><span class="toc-nav-text">1.错误日志(log_error)</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-1-作用"><span class="toc-nav-text">1.1 作用</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-2-错误日志配置"><span class="toc-nav-text">1.2 错误日志配置</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#1-3-日志内容查看"><span class="toc-nav-text">1.3 日志内容查看</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#2-binlog-binary-logs-二进制日志"><span class="toc-nav-text">2. binlog(binary logs):二进制日志 *****</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-1-作用"><span class="toc-nav-text">2.1 作用</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-2-binlog配置-5-7必须加server-id"><span class="toc-nav-text">2.2 binlog配置 (5.7必须加server_id)</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-2-1-创建日志目录"><span class="toc-nav-text">2.2.1 创建日志目录</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-2-2-修改配置文件"><span class="toc-nav-text">2.2.2 修改配置文件</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-2-3-重启数据库生效"><span class="toc-nav-text">2.2.3 重启数据库生效</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-2-4-参数说明"><span class="toc-nav-text">2.2.4 参数说明</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-3-binlog记录了什么"><span class="toc-nav-text">2.3 binlog记录了什么?</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-3-0-引入"><span class="toc-nav-text">2.3.0 引入</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-3-1-记录SQL语句种类"><span class="toc-nav-text">2.3.1 记录SQL语句种类</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-3-2-DML三种记录方式"><span class="toc-nav-text">2.3.2 DML三种记录方式</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-3-3-面试题"><span class="toc-nav-text">2.3.3 面试题</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-4-event（事件）是什么"><span class="toc-nav-text">2.4 event（事件）是什么?</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-4-1-事件的简介"><span class="toc-nav-text">2.4.1 事件的简介</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-4-2-event的组成"><span class="toc-nav-text">2.4.2 event的组成</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-5-日志文件查看"><span class="toc-nav-text">2.5 日志文件查看</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-5-1-查看日志的开启情况"><span class="toc-nav-text">2.5.1 查看日志的开启情况</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-5-2-查看一共多少个binlog"><span class="toc-nav-text">2.5.2 查看一共多少个binlog</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-5-3-查看mysql正在使用的日志文件"><span class="toc-nav-text">2.5.3 查看mysql正在使用的日志文件</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-6-日志内容查看"><span class="toc-nav-text">2.6 日志内容查看</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-6-1-event查看"><span class="toc-nav-text">2.6.1 event查看</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-6-2-binlog文件内容详细查看"><span class="toc-nav-text">2.6.2 binlog文件内容详细查看</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-7-基于Position号进行日志截取"><span class="toc-nav-text">2.7 基于Position号进行日志截取</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-8-binlog日志的GTID新特性"><span class="toc-nav-text">2.8 binlog日志的GTID新特性</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-8-1-GTID-介绍"><span class="toc-nav-text">2.8.1 GTID 介绍</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-8-2-GTID-Global-Transaction-ID"><span class="toc-nav-text">2.8.2. GTID(Global Transaction ID)</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-8-3-基于GTID进行查看binlog"><span class="toc-nav-text">2.8.3. 基于GTID进行查看binlog</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-8-4-GTID的幂等性"><span class="toc-nav-text">2.8.4  GTID的幂等性</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-9-使用二进制日志恢复数据案例"><span class="toc-nav-text">2.9 使用二进制日志恢复数据案例</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-9-1-故障环境介绍"><span class="toc-nav-text">2.9.1 故障环境介绍</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-9-2-恢复过程-无GTID时的恢复"><span class="toc-nav-text">2.9.2 恢复过程(无GTID时的恢复)</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-9-3-有GTID的恢复"><span class="toc-nav-text">2.9.3 有GTID的恢复:</span></a></li></ol></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#2-10二进制日志其他操作"><span class="toc-nav-text">2.10二进制日志其他操作</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-10-1-自动清理日志"><span class="toc-nav-text">2.10.1 自动清理日志</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-10-2-手工清理"><span class="toc-nav-text">2.10.2 手工清理</span></a></li><li class="toc-nav-item toc-nav-level-3"><a class="toc-nav-link" href="#2-10-3-日志是怎么滚动"><span class="toc-nav-text">2.10.3 日志是怎么滚动</span></a></li></ol></li></ol></li><li class="toc-nav-item toc-nav-level-1"><a class="toc-nav-link" href="#3-slow-log-慢日志"><span class="toc-nav-text">3.slow_log 慢日志</span></a><ol class="toc-nav-child"><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-1-作用"><span class="toc-nav-text">3.1 作用:</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-2-开启慢日志-默认没开启"><span class="toc-nav-text">3.2 开启慢日志(默认没开启)</span></a></li><li class="toc-nav-item toc-nav-level-2"><a class="toc-nav-link" href="#3-3-mysqldumpslow-分析慢日志"><span class="toc-nav-text">3.3 mysqldumpslow 分析慢日志</span></a></li></ol></li></ol>
    
  </div>
</aside>
  
  <!-- Catalog END -->
</main>

<script>
  (function () {
    var url = 'http://www.liuqingzheng.top/db/MySQL系列/06-MySQL系列之-日志管理/';
    var banner = ''
    if (banner !== '' && banner !== 'undefined' && banner !== 'null') {
      $('#article-banner').css({
        'background-image': 'url(' + banner + ')'
      })
    } else {
      $('#article-banner').geopattern(url)
    }
    $('.header').removeClass('fixed-header')

    // error image
    $(".markdown-content img").on('error', function() {
      $(this).attr('src', 'http://file.muyutech.com/error-img.png')
      $(this).css({
        'cursor': 'default'
      })
    })

    // zoom image
    $(".markdown-content img").on('click', function() {
      var src = $(this).attr('src')
      if (src !== 'http://file.muyutech.com/error-img.png') {
        var imageW = $(this).width()
        var imageH = $(this).height()

        var zoom = ($(window).width() * 0.95 / imageW).toFixed(2)
        zoom = zoom < 1 ? 1 : zoom
        zoom = zoom > 2 ? 2 : zoom
        var transY = (($(window).height() - imageH) / 2).toFixed(2)

        $('body').append('<div class="image-view-wrap"><div class="image-view-inner"><img src="'+ src +'" /></div></div>')
        $('.image-view-wrap').addClass('wrap-active')
        $('.image-view-wrap img').css({
          'width': `${imageW}`,
          'transform': `translate3d(0, ${transY}px, 0) scale3d(${zoom}, ${zoom}, 1)`
        })
        $('html').css('overflow', 'hidden')

        $('.image-view-wrap').on('click', function() {
          $(this).remove()
          $('html').attr('style', '')
        })
      }
    })
  })();
</script>


  <script>
    var qr = new QRious({
      element: document.getElementById('share-qrcode'),
      value: document.location.href
    });
  </script>



  <script>
    var gitmentConfig = "liuqingzheng";
    if (gitmentConfig !== 'undefined') {
      var gitment = new Gitment({
        id: "db/MySQL系列/06-MySQL系列之-日志管理",
        owner: "liuqingzheng",
        repo: "FuckBlog",
        oauth: {
          client_id: "32a4076431cf39d0ecea",
          client_secret: "94484bd79b3346a949acb2fda3c8a76ce16990c6"
        },
        theme: {
          render(state, instance) {
            const container = document.createElement('div')
            container.lang = "en-US"
            container.className = 'gitment-container gitment-root-container'
            container.appendChild(instance.renderHeader(state, instance))
            container.appendChild(instance.renderEditor(state, instance))
            container.appendChild(instance.renderComments(state, instance))
            container.appendChild(instance.renderFooter(state, instance))
            return container;
          }
        }
      })
      gitment.render(document.getElementById('comments'))
    }
  </script>




    <div class="scroll-top">
  <span class="arrow-icon"></span>
</div>
    <footer class="app-footer">
<!-- 不蒜子统计 -->
<span id="busuanzi_container_site_pv">
     本站总访问量<span id="busuanzi_value_site_pv"></span>次
</span>
<span class="post-meta-divider">|</span>
<span id="busuanzi_container_site_uv" style='display:none'>
     本站访客数<span id="busuanzi_value_site_uv"></span>人
</span>
<script async src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>



  <p class="copyright">
    &copy; 2021 | Proudly powered by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank">小猿取经</a>
    <br>
    Theme by <a href="https://www.cnblogs.com/xiaoyuanqujing" target="_blank" rel="noopener">小猿取经</a>
  </p>
</footer>

<script>
  function async(u, c) {
    var d = document, t = 'script',
      o = d.createElement(t),
      s = d.getElementsByTagName(t)[0];
    o.src = u;
    if (c) { o.addEventListener('load', function (e) { c(null, e); }, false); }
    s.parentNode.insertBefore(o, s);
  }
</script>
<script>
  async("//cdnjs.cloudflare.com/ajax/libs/fastclick/1.0.6/fastclick.min.js", function(){
    FastClick.attach(document.body);
  })
</script>

<script>
  var hasLine = 'true';
  async("//cdnjs.cloudflare.com/ajax/libs/highlight.js/9.12.0/highlight.min.js", function(){
    $('figure pre').each(function(i, block) {
      var figure = $(this).parents('figure');
      if (hasLine === 'false') {
        figure.find('.gutter').hide();
      }
      var lang = figure.attr('class').split(' ')[1] || 'code';
      var codeHtml = $(this).html();
      var codeTag = document.createElement('code');
      codeTag.className = lang;
      codeTag.innerHTML = codeHtml;
      $(this).attr('class', '').empty().html(codeTag);
      figure.attr('data-lang', lang.toUpperCase());
      hljs.highlightBlock(block);
    });
  })
</script>





<!-- Baidu Tongji -->

<script>
    var _baId = 'c5fd96eee1193585be191f318c3fa725';
    // Originial
    var _hmt = _hmt || [];
    (function() {
      var hm = document.createElement("script");
      hm.src = "//hm.baidu.com/hm.js?" + _baId;
      var s = document.getElementsByTagName("script")[0];
      s.parentNode.insertBefore(hm, s);
    })();
</script>


<script src="/js/script.js"></script>


<script src="/js/search.js"></script>


<script src="/js/load.js"></script>



  <span class="local-search local-search-google local-search-plugin" style="right: 50px;top: 70px;;position:absolute;z-index:2;">
      <input type="search" placeholder="站内搜索" id="local-search-input" class="local-search-input-cls" style="">
      <div id="local-search-result" class="local-search-result-cls"></div>
  </span>


  </body>
</html>